<?php

//设置连接属性
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8';
$user = 'root';
$pass = 'qaz123';
// //连接
// $options = array(
//     PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
//     PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
// );

try {
    //建立连接
    $pdo = new PDO($dsn, $user, $pass);
    //     //执行 sql 查询
    //     // $sql = 'show tables';
    //     // $sql = 'select * from `post` where id = 1';
    //     $sql = 'select * from `post` order by `id` desc';
    //     // $sql = strtoupper($sql);
    //     // $sql = $pdo->quote($sql);

    //     // print_r($pdo);
    //     $stmt = $pdo->query($sql);
    //     $rows = $stmt->fetchAll();


    //     // var_dump($rows);
    //     //打印查询结果
    //     echo '<pre/>';
    //     foreach ($rows as $row) {
    //         print_r($row);
    //     }
} catch (PDOException $exception) {
    printf("Error: %s\n", $exception->getMessage());
}

class post
{
    public $id;
    public $title;
    public $content;
    public $created_at;

    protected $pdo;

    public function __construct(PDO $pdo = null)
    {
        if ($pdo != null) {
            $this->pdo = $pdo;
        }
    }

    public function insert($title, $content)
    {
        $sql = 'INSERT INTO `post` (title, content, created_at) VALUES (:title, :content, :created_at)';
        try {
            $stmt = $this->pdo->prepare($sql);
            $datetime = date('Y-m-d H:i:s', time());
            //绑定参数值
            $stmt->bindParam(':title', $title, PDO::PARAM_STR);
            $stmt->bindParam(':content', $content, PDO::PARAM_STR);
            $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR);
            //执行语句
            $stmt->execute();
            return $this->pdo->lastInsertId(); //返回插入记录对应ID
        } catch (PDOException $e) {
            printf("数据库插入失败：%s\n", $e->getMessage());
        }
    }

    public function select($id)
    {
        $sql = 'select * from `post` where id = ?';
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchObject(self::class); //以对象方式返回结果集
        } catch (PDOException $e) {
            printf("数据库查询失败：%s\n", $e->getMessage());
        }
    }

    public function selectAll()
    {
        $sql = 'select * from `post` order by id desc';
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_CLASS, self::class);
        } catch (PDOException $e) {
            printf("数据库查询失败：%s\n", $e->getMessage());
        }
    }

    public function update($id)
    {
        $sql = 'update `post` set created_at = :created_at where id = :id';
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR);
            $stmt->bindValue(':id', $id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt->rowCount();
        } catch (PDOException $e) {
            printf("数据库更新失败：%s\n", $e->getMessage());
        }
    }

    public function delete($id)
    {
        $sql = 'delete from `post` where id = ?';
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(1, $id, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt->rowCount();
        } catch (PDOException $e) {
            printf("数据库更新失败：%s\n", $e->getMessage());
        }
    }

    // 插入事务
    public function batchInsert(array $items)   
    {
        $sql = 'insert into `post` (title, content, created_at) values (:title, :content, :created_at)';
        try {
            $this->pdo->beginTransaction();
            $stmt = $this->pdo->prepare($sql);
            foreach ($items as $item) {
                $datetime  = date('Y-m-d H:i:s', time());
                $stmt->bindParam(':title', $item['title'], PDO::PARAM_STR);
                $stmt->bindParam(':content', $item['content'], PDO::PARAM_STR);
                $stmt->bindParam(':created_at', $datetime, PDO::PARAM_STR);
                //执行语句
                $stmt->execute();
            }
            $this->pdo->commit();
            return $stmt->rowCount();// 提交事务
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            printf("数据库批量插入失败：%s\n", $e->getMessage());
        }
    }
}

// //测试代码
// $post = new Post($pdo);
// // insert
// $title = '这是一篇测试文章👿';
// $content = '测试内容：今天天气不错☀';
// $id = $post->insert($title, $content);
// echo '文章插入成功：' . $id . '<br>';
// //select
// $item = $post->select($id);
// echo '<pre>';
// print_r($item);
// // update
// $affected = $post->update($id);
// echo '受影响的行数：' . $affected . '<br>';
// //delete
// $affected = $post->delete($id);
// echo '受影响的行数：' . $affected . '<br>';
// //selectAll
// $items = $post->selectAll();
// print_r($items);

// 测试插入事务
$post = new Post($pdo);
$items = [
    [
        'title' => '这是一篇测试文章111',
        'content' => '测试内容'
    ],
    [
        'title' => '这是一篇测试文章222',
        'content' => '测试内容'
    ],
    [
        'title' => '这是一篇测试文章333',
        'content' => '测试内容'
    ],
];
$post->batchInsert($items);
$items = $post->selectAll();
print_r($items);